

SELECT
   z.patno,
   z.grantyear,
   z.asg_usgovt CATEGORY_1_OWNED_BY_USGOVT,
   IF(z.patno IN 
   (
      select
         patno 
      from
         uspto_patno_govtack 
   )

AND z.patno IN
(
	select patno from govtz where USG=1 and matched='DIRECT'
)


	, 1, 0) CATEGORY_2_ACKNOWLEDGE_USGOVT_SUPPORT,




   SUM(IF( n1.cited IN 
   (
      select
         patno 
      from
         govtz 
      where
         ASG_USGOVT = 1
   ) 
, 1, 0)) CATEGORY_3_CITING_A_PATENT_OWNED_BY_USGOVT,

   SUM(IF(  n1.cited IN 
   (
      select
         patno 
      from
         uspto_patno_govtack
   ) 

AND n1.cited IN
(
	select patno from govtz where USG=1 and matched='DIRECT'
)

, 1, 0)) CATEGORY_4_CITING_A_PATENT_THAT_ACKNOWLEDGES_USGOVT_SUPPORT,
   SUM(IF(n1.wosid IN 
   (
      select
         wosid 
      from
         wos_us_support_affl
   )
, 1, 0)) CATEGORY_5_CITING_A_WOS_PAPER_AUTHORED_BY_USGOVT,
   SUM(IF(n1.wosid IN 
   (
      select
         wosid 
      from
         wos_us_support_fund
   )

, 1, 0)) CATEGORY_6_CITING_A_WOS_PAPER_THAT_ACKNOWLEDGES_USGOVT_SUPPORT,
   1 
FROM
   govtz z 
   LEFT JOIN
      npl_temp_9 n1 
      ON z.patno = n1.patno 
WHERE
   1 = 1 
group by
   z.patno ;

